Amazon QuickSightからEC2上のPostgreSQLにアクセスする

quiver

2018.11.02

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Amazon QuickSight Enterprise EditionではVPC内のデータにアクセスできます。

この機能を利用することで

Amazon RDS・Amazon Redshift
EC2 上に構築したデータベース

にパブリック IP アドレスが割り振られていないケースでも、 QuickSight のデータソースに利用できます。

Amazon Web Services ブログには、RDS PostgreSQL を例にした VPC 接続方法が解説されています。

Amazon QuickSightのプライベートVPC内のデータアクセスの設定方法について | Amazon Web Services

今回は、このバリエーションとして、EC2 上の PostgreSQL に対して接続してみます。

実質的な手順は AWS ブログと同じです。元ブログを参考に、EC2 上のデータストアへの接続を試してみたら、ハマることなく無事疎通できたことの記録としてお読みください。

構成図も元ブログから大いにインスパイアされています。

イメージ図

QuickSight のデータソースとして Amazon Linux 2 上の PostgreSQL 9.2 を利用します。

ポイント

設定上のポイントは以下です。

Amazon QuickSight の VPC アクセスでは Elastic Network Interface(ENI) が作成される
QuickSight の VPC 通信用のセキュリティグループを新規に作成
QuickSight から PostgreSQL on EC2 への通信では、PostgreSQL on EC2 のセキュリティグループの inbound rule に QuickSight のセキュリティグループを指定
PostgreSQL on EC2 からの戻りの通信はステートレス。そのため、QuickSight のセキュリティグループの inbound rule に PostgreSQL on EC2 のセキュリティグループを指定

特に、最後のリターントラフィックがステートレスであることにご注意ください。

前提

Amazon QuickSight Enterprise Edition を契約している
QuickSight の管理者は以下の権限がある
quicksight:CreateVPCConnection
ec2:CreateNetworkInterface

事前準備:EC2 に PostgreSQL をインストール

Amazon Linux 2 に PostgreSQL 9.2 をインストールします。

インスタンスの用意

Amazon Linux2 で EC2 を起動します。このインスタンスには、セキュリティグループ「PostgreSQL-access」をアタッチします。

Inbound Rule には以下のルールを追加します。

Protocol : TCP(PostgreSQL)
Port Range : 5432
Source : VPC の CIDR

root権限:PostgreSQL のインストール

PostgreSQL のインストール

$ sudo yum install -y postgresql-server

データの初期化

$ sudo -u postgres postgresql-setup initdb
Initializing database ... OK
$ sudo systemctl start postgresql.service
$

postgresユーザー:PostgreSQLのデータベース・テーブル作成

postgres ユーザーで

データベース : dbname
テーブル : foo

を作成します。

$ sudo su - postgres
$ psql
psql (9.2.24)
Type "help" for help.
postgres=#
postgres=# create database dbname;
CREATE DATABASE
postgres=# \connect dbname;
You are now connected to database "dbname" as user "postgres".
dbname=# create table foo(a int);
CREATE TABLE
dbname=# insert into foo values(1);
INSERT 0 1

postgresユーザー権限:VPC内からPostgreSQL にアクセスできるように設定変更

pg_hba.conf:パスワード認証のための変更

$ diff -u pg_hba.conf.orig pg_hba.conf
--- pg_hba.conf.orig	2018-10-28 18:22:38.326738552 +0000
+++ pg_hba.conf	2018-10-28 18:23:45.046444268 +0000
@@ -79,6 +79,7 @@
 # "local" is for Unix domain socket connections only
 local   all             all                                     peer
 # IPv4 local connections:
+host    all             all             172.31.0.0/16            md5
 host    all             all             127.0.0.1/32            ident
 # IPv6 local connections:
 host    all             all             ::1/128                 ident

172.31.0.0/16 は対象 VPC の CIDR です。

pg_hba.conf : localhost 以外からの接続のため

$ diff -u postgresql.conf.orig postgresql.conf
--- postgresql.conf.orig	2018-10-28 18:22:45.006709089 +0000
+++ postgresql.conf	2018-10-28 18:25:06.362078497 +0000
@@ -56,6 +56,7 @@

 # - Connection Settings -

+listen_addresses = '*'		# what IP address(es) to listen on;
 #listen_addresses = 'localhost'		# what IP address(es) to listen on;
 					# comma-separated list of addresses;
 					# defaults to 'localhost'; use '*' for all

postgresユーザー権限:QuickSight 接続用ユーザーの作成

$ createuser -P -s -e username
Enter password for new role:
Enter it again:
CREATE ROLE username PASSWORD 'XXX' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

QuickSight 用サブネットからの接続確認

設定を反映するために、PostgreSQL をリスタートします。

$ sudo systemctl restart postgresql.service

QuickSight 用セキュリティグループを適用した EC2 から PostgreSQL に接続できることを確認します。

$ psql -h 172.31.4.110 -U username  dbname # 172.31.4.110 は PostgreSQL on EC2 のプライベートIPアドレス
Password for user username:
psql (9.2.24)
Type "help" for help.

dbname=# select * from foo;
 a
---
 1
(1 row)

dbname=#

以上で事前準備は完了です。

1.QuickSight用のセキュリティグループ作成

QuickSight の ENI にアタッチするセキュリティグループを作成します。

名前を「Amazon-QuickSight-access」とします。

2．QuickSight から PostgreSQL へのトラフィック

QuickSight からPostgreSQL on EC2 のセキュリティグループ(PostgreSQL-access)に QuickSight のセキュリティグループ(Amazon-QuickSight-access)経由のアクセスを許可します。

セキュリティグループ PostgreSQL-access の Inbound Rule に以下のルールを追加します。

Protocol : TCP
Port Range : PostgreSQL
Source : 「Amazon-QuickSight-access」 ※ QuickSight ENI 用セキュリティグループ

3．QuickSight から PostgreSQL へのトラフィックの戻り

Amazon QuickSight ENI にアタッチされているセキュリティグループはステートフルではありません。つまり、送信先ホストからの戻りトラフィックは自動的に許可されません。

Amazon QuickSight Elastic Network Interface にアタッチされているセキュリティグループは、ほとんどのセキュリティグループとは動作が異なります。セキュリティグループは通常ステートフルです。つまり、送信接続が確立されると、送信先ホストからのリターントラフィックが自動的に許可されます。ただし、Amazon QuickSight ネットワークインターフェイスにアタッチされているセキュリティグループはステートフルではありません。つまり、送信先ホストからの戻りトラフィックは自動的に許可されません。この場合、ネットワークインターフェイスセキュリティグループに Egress ルールを追加しても機能しません。したがって、明示的に承認するために、受信ルールをセキュリティグループに追加する必要があります。

すべての受信リターンパケットの送信先ポート番号はランダムに割り当てられたポート番号に設定されているため、セキュリティグループの受信ルールはすべてのポート (0–65535) のトラフィックを許可する必要があります。Amazon QuickSight がどのインスタンスに接続できるかを制限しない場合は、すべてのポートでトラフィックを 0.0.0.0/0 にする受信ルールを使用してこのセキュリティグループを設定できます。特定のインスタンスにのみ接続するように Amazon QuickSight を制限する場合は、セキュリティグループ ID を指定できます (推奨)。または、受信セキュリティグループルールで許可するインスタンスのプライベート IP アドレスを指定することもできます。この場合、インバウンドセキュリティグループルールでは、すべてのポートでトラフィックを許可する必要があります。

https://docs.aws.amazon.com/quicksight/latest/user/working-with-aws-vpc.html

そのため、送信先ホストからの戻りトラフィックをこのセキュリティグループ(Amazon-QuickSight-access)のインバウンドルールで許可します。すべての受信リターンパケットの送信先ポート番号はランダムに割り当てられたポート番号に設定されているため、セキュリティグループの受信ルールはすべてのポート (0–65535) のトラフィックを許可します。